<?php

namespace App\Helpers;

class QueryHelp
{
    /**
     * @query Builder query;
     * @filters Request|array
     * @fields allowed to filter fields
     */
    public static function filter($query, array $filters = [], $fields = [])
    {
        $fields = str2arr($fields);
        if ($fields) {
            $filters = array_intersect_key($filters, array_flip($fields));
        }
        if (empty($filters)) return $query;
        $wheres = [
            '<>', '>', '<', '!<', '!>', '!=', '>=', '<=', '=>', '=<', 'null', 'notnull', 'ornull', 'ornotnull', 'where', 'or', 'like', 'notlike', 'orlike', 'ornotlike', 'in', 'notin', 'orin', 'ornotin', 'bt', 'notbt', 'orbt', 'ornotbt', 'date', 'year', 'month', 'day', 'time', 'col', 'orcol', 'tag', 'ortag',
            'orwhere', 'wherein', 'nowheretin', 'orwherein', 'orwherenotin', 'wherenull', 'wherenotnull', 'orwherenull', 'orwherenotnull', 'wherelike', 'wherenotlike', 'orwherelike', 'orwherenotlike', 'wherebt', 'wherenotbt', 'orwherebt', 'orwherenotbt', 'wheredate', 'whereyear', 'wheremonth', 'whereday', 'wheretime', 'wherecol', 'orwherecol', 'wheretag', 'orwheretag'
        ];

        foreach ($filters as $field => $val) {
            if (is_array($val) || in_array($field, ['page', 'per_page', 'page_type', 'limit', 'sort', 'include', 'fields', 'has', 'orhas', 'wherehas', 'orwherehas', 'wherecount', 'scope'])) continue;
            if ($val && $val[0] === '@') {  //以@开头的直接去@头查询
                $query = $query->where($field, mb_substr($val, 1));
                continue;
            }
            if ($field && $field[0] === '@') {  //当字段名是page,limit等时采用的方式
                $field = mb_substr($field, 1);
            }
            if (!$field) continue;
            $param = explode(':', $val, 3);

            if (empty($param)) continue;
            $where = $param[0];

            if (!in_array($where, $wheres)) {
                $query = $query->where($field, $val);
                continue;
            }

            if (count($param) === 1) {
                switch ($where) {
                    case 'null':
                        $query = $query->whereNull($field);
                        break;
                    case 'ornull':
                        $query = $query->orWhereNull($field);
                        break;
                    case 'notnull':
                        $query = $query->whereNotNull($field);
                        break;
                    case 'ornotnull':
                        $query = $query->orWhereNotNull($field);
                        break;
                    default:
                        $query = $query->where($field, $val);
                        break;
                }
            } elseif (count($param) === 2) {
                switch ($where) {
                    case '<>':
                    case '!=':
                    case '!>':
                    case '!<':
                    case '<':
                    case '>':
                    case '<=':
                    case '>=':
                    case '=>':
                    case '=<':
                        $query = $query->where($field, $where, $param[1]);
                        break;
                    case 'where':
                        $query = $query->where($field, $param[1]);
                        break;
                    case 'or':
                    case 'orwhere':
                        $query = $query->orWhere($field, $param[1]);
                        break;
                    case 'like':
                    case 'wherelike':
                        $query = $query->where($field, 'like', '%' . $param[1] . '%');
                        break;
                    case 'notlike':
                    case 'wherenotlike':
                        $query = $query->where($field, 'not like', '%' . $param[1] . '%');
                        break;
                    case 'orlike':
                    case 'orwherelike':
                        $query = $query->orWhere($field, 'like', '%' . $param[1] . '%');
                        break;
                    case 'ornotlike':
                    case 'orwherenotlike':
                        $query = $query->orWhere($field, 'not like', '%' . $param[1] . '%');
                        break;
                    case 'in':
                    case 'wherein':
                        $query = $query->whereIn($field, explode(',', $param[1]));
                        break;
                    case 'notin':
                    case 'wherenotin':
                        $query = $query->whereNotIn($field, explode(',', $param[1]));
                        break;
                    case 'orin':
                    case 'orwherein':
                        $query = $query->orWhereIn($field, explode(',', $param[1]));
                        break;
                    case 'ornotin':
                    case 'orwherenotin':
                        $query = $query->orWhereNotIn($field, explode(',', $param[1]));
                        break;
                    case 'tag':
                    case 'wheretag':
                        $query = $query->whereRaw('FIND_IN_SET(?,' . $field . ')', explode(',', $param[1]));
                        break;
                    case 'ortag':
                    case 'orwheretag':
                        $query = $query->orWhereRaw('FIND_IN_SET(?,' . $field . ')', explode(',', $param[1]));
                        break;
                    case 'bt':
                    case 'wherebetween':
                        $query = $query->whereBetween($field, explode(',', $param[1], 2));
                        break;
                    case 'orbt':
                    case 'orwherebetween':
                        $query = $query->orWhereBetween($field, explode(',', $param[1], 2));
                        break;
                    case 'notbt':
                    case 'wherenotbetween':
                        $query = $query->whereNotBetween($field, explode(',', $param[1], 2));
                        break;
                    case 'ornotbt':
                    case 'orwherenotbetween':
                        $query = $query->orWhereNotBetween($field, explode(',', $param[1], 2));
                        break;
                    case 'date':
                    case 'wheredate':
                        if (strtotime($param[1])) {
                            $query = $query->whereDate($field, $param[1]);
                        }
                        break;
                    case 'year':
                    case 'whereyear':
                        if (is_numeric($param[1])) {
                            $query = $query->WhereYear($field, $param[1]);
                        }
                        break;
                    case 'month':
                    case 'wheremonth':
                        if (is_numeric($param[1])) {
                            $query = $query->whereMonth($field, $param[1]);
                        }
                        break;
                    case 'day':
                    case 'whereday':
                        if (is_numeric($param[1])) {
                            $query = $query->WhereDay($field, $param[1]);
                        }
                        break;
                    case 'time':
                    case 'wheretime':
                        if (strtotime($param[1])) {
                            $query = $query->whereTime($field, $param[1]);
                        }
                        break;
                    case 'col':
                    case 'wherecolumn':  //限止不允许查询字段判断
                        if (empty($fields) || in_array($param[1], $fields)) {
                            $query = $query->whereColumn($field, $param[1]);
                        }
                        break;
                    case 'orcol':
                    case 'orwherecolumn':
                        if (empty($fields) || in_array($param[1], $fields)) {
                            $query = $query->orWhereColumn($field, $param[1]);
                        }
                        break;
                    default:
                        $query = $query->where($field, $val);
                        break;
                }
            } elseif (in_array($param[1], ['<>', '>', '<', '!<', '!>', '!=', '>=', '<=', '=>', '=<'])) {
                switch ($where) {
                    case 'where':
                        $query = $query->where($field, $param[1], $param[2]);
                        break;
                    case 'or':
                    case 'orwhere':
                        $query = $query->orWhere($field, $param[1], $param[2]);
                        break;
                    case 'date':
                    case 'wheredate':
                        if (strtotime($param[2])) {
                            $query = $query->whereDate($field, $param[1], $param[2]);
                        }
                        break;
                    case 'year':
                    case 'whereyear':
                        if (is_numeric($param[2])) {
                            $query = $query->WhereYear($field, $param[1], $param[2]);
                        }
                        break;
                    case 'month':
                    case 'wheremonth':
                        if (is_numeric($param[2])) {
                            $query = $query->whereMonth($field, $param[1], $param[2]);
                        }
                        break;
                    case 'day':
                    case 'whereday':
                        if (is_numeric($param[2])) {
                            $query = $query->WhereDay($field, $param[1], $param[2]);
                        }
                        break;
                    case 'time':
                    case 'wheretime':
                        if (strtotime($param[2])) {
                            $query = $query->whereTime($field, $param[1], $param[2]);
                        }
                        break;
                    case 'col':
                    case 'wherecolumn':
                        if (empty($fields) || in_array($param[2], $fields)) {
                            $query = $query->whereColumn($field, $param[1], $param[2]);
                        }
                        break;
                    case 'orcol':
                    case 'orwherecolumn':
                        if (empty($fields) || in_array($param[2], $fields)) {
                            $query = $query->orWhereColumn($field, $param[1], $param[2]);
                        }
                        break;
                    default:
                        $query = $query->where($field, $val);
                        break;
                }
            } else {
                $query = $query->where($field, $val);
            }
        }
        return $query;
    }

    public static function has($query, $has, $or = false)
    {
        if (empty($has)) return $query;
        foreach ($has as $reltion => $val) {
            $opt = $or ? 'orHas' : 'has';
            $varr = explode(':', $val);

            if (count($varr) === 1 && (int) $val >= 0) {
                if ($val == 0) {
                    $query = $query->$opt($reltion, '=', 0);
                } else {
                    $query = $query->$opt($reltion, '>=', (int) $val);
                }
            } elseif (in_array($varr[0], ['<>', '>', '<', '!<', '!>', '!=', '>=', '<=', '=>', '=<', '=']) && (int) $varr[1] > 0) {
                $query = $query->$opt($reltion, $varr[0], $varr[1]);
            }
        }
        return $query;
    }

    public function scope($query, $scope, $param = [])
    {
        return $query->$scope(...$param);
    }

    public static function sort($query, $sort)
    {
        $sorts = str2arr($sort);
        foreach ($sorts as $key => $s) {
            if ($s[0] === '-') {
                $query = $query->orderBy(ltrim($s, '-'), 'desc');
            } else {
                $query = $query->orderBy($s, 'asc');
            }
        }
        return $query;
    }
}
